BizSpring Growth Platform™️을 자세히 알아보자 – TAM (2)

				
					WITH
e_profile AS (
    ...
)
,e_crm_member AS (
    ...
)

,e_rfm_model AS (
    SELECT      *
    FROM        `bizspring.rfm_model`
)

,e_user_rfm AS (
    SELECT      client_seq,pfno,dpuid,stat_date
                ,recency,frequency,monetary
    FROM        `bizspring.user_rfm_*`
    LEFT JOIN   e_profile USING(client_seq,pfno)
)

,t_user_rfm_p AS (
    SELECT      client_seq,STRING_AGG(DISTINCT pfno ORDER BY pfno ASC) AS pfno,dpuid
                ,recency
                ,SUM(frequency) AS frequency
                ,SUM(monetary ) AS monetary
    FROM (
        SELECT  client_seq,pfno,dpuid
                ,LAST_VALUE(recency) OVER(user_window) AS recency
                ,frequency,monetary
        FROM    e_user_rfm
        WINDOW  user_window AS (
                PARTITION BY client_seq,dpuid ORDER BY stat_date ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
    )
    GROUP BY 1,3,4
)
,t_user_rfm AS (
    SELECT      client_seq,pfno,dpuid
                ,IF(rfm_r IS NULL OR rfm_r<0.2,"Lv.1",IF(rfm_r<0.4,"Lv.2",IF(rfm_r<0.6,"Lv.3",IF(rfm_r<0.8,"Lv.4","Lv.5")))) AS rfm_r
                ,IF(rfm_f IS NULL OR rfm_f<0.2,"Lv.1",IF(rfm_f<0.4,"Lv.2",IF(rfm_f<0.6,"Lv.3",IF(rfm_f<0.8,"Lv.4","Lv.5")))) AS rfm_f
                ,IF(rfm_m IS NULL OR rfm_m<0.2,"Lv.1",IF(rfm_m<0.4,"Lv.2",IF(rfm_m<0.6,"Lv.3",IF(rfm_m<0.8,"Lv.4","Lv.5")))) AS rfm_m
    FROM (
        SELECT  client_seq,pfno,dpuid
                ,PERCENT_RANK() OVER(PARTITION BY client_seq ORDER BY recency  DESC) AS rfm_r
                ,PERCENT_RANK() OVER(PARTITION BY client_seq ORDER BY frequency ASC) AS rfm_f
                ,PERCENT_RANK() OVER(PARTITION BY client_seq ORDER BY monetary  ASC) AS rfm_m
        FROM    t_user_rfm_p
    )
)

,t_rfm_cluster AS (
    SELECT      client_seq,pfno,dpuid,rfm
    FROM        t_user_rfm
    LEFT JOIN   e_rfm_model USING(rfm_r,rfm_f,rfm_m)
)

SELECT  dpuid
FROM    t_rfm_cluster
WHERE   rfm = 'RFM Segment(Superusers/Warm leads/Do not lose and need attention/Lost)';